//
//  dbAccess.m
//  MoneyLog
//
//  Created by Max on 11-3-9.
//  Copyright 2011 __MyCompanyName__. All rights reserved.
//

#import "Database.h"
#import "FMDatabase.h"
#import "SQLiteHelper.h"

#import "Record.h"
#import "RecordWrapper.h"
#import "KindWrapper.h"
#import "AccountWrapper.h"
#import "TypeWrapper.h"
#import "PieChartItem.h"

#import "MyCategory.h"

Database *g_Database;

@implementation Database

#pragma mark -
#pragma mark 方法

-(id)init{
	if (self==super.init) {
	}
	
	return self;
}

+(void)initialize {
    static BOOL initialized = NO;
    if(!initialized)
    {
        initialized = YES;
        g_Database = [[Database alloc] init];
    }
}

-(void)close 
{
    [SQLiteHelper closeDatabase];
}

#pragma  mark - * Kind Table
// get kind
-(NSMutableArray *) getKind:(NSString *)strWhere{
	//SQL
	NSString *sql=@"SELECT * FROM kind ";
    NSMutableArray *array=[[NSMutableArray alloc] init];
	
	if (strWhere !=nil) {
		sql=[sql stringByAppendingString:@" WHERE "];
		sql=[sql stringByAppendingString:strWhere];
	} 
	sql = [sql stringByAppendingString:@" ORDER BY id ASC"];
    
	FMResultSet *resultSet=[[SQLiteHelper getDatabase] executeQuery:sql];
	while ( [resultSet next] ) { 
        KindWrapper *kind = [[KindWrapper alloc] init];
        
        kind.id = [resultSet intForColumn:@"id"];
        kind.kindName = [resultSet stringForColumn:@"kindname"];
        kind.name = [resultSet stringForColumn:@"name"];
        kind.parentId = [resultSet intForColumn:@"parentid"];
        kind.type = [resultSet stringForColumn:@"type"];
        kind.useTimes = [resultSet intForColumn:@"usetimes"];
        kind.mark = [resultSet boolForColumn:@"mark"];
        
        if (kind.kindName ==nil) {
			continue;
		} 
        [array addObject:kind];
        [kind release]; 
	} 	   
	
	return array;
}


//common use kind
-(NSMutableArray *) getCommonKind:(int)count{
	//SQL
	NSString *sql=[NSString stringWithFormat:@"SELECT * FROM kind WHERE parentid=0 ORDER BY usetimes DESC "];
	NSMutableArray *array=[[NSMutableArray alloc] init];
	FMResultSet *resultSet=[[SQLiteHelper getDatabase] executeQuery:sql];

	while ( [resultSet next] && [array count] < count ) { 
        KindWrapper *kind = [[KindWrapper alloc] init];
        
        kind.id = [resultSet intForColumn:@"id"];
        kind.kindName = [resultSet stringForColumn:@"kindname"];
        kind.name = [resultSet stringForColumn:@"name"];
        kind.parentId = [resultSet intForColumn:@"parentid"];
        kind.type = [resultSet stringForColumn:@"type"];
        kind.useTimes = [resultSet intForColumn:@"usetimes"];
        kind.mark = [resultSet boolForColumn:@"mark"];
    
        if (kind.kindName ==nil) {
			continue;
		} 
        [array addObject:kind];
        [kind release]; 
	} 	   
	
	return array;
}

-(NSMutableArray *) getMarkKind{
	//SQL
	NSString *sql=[NSString stringWithFormat:@"SELECT * FROM kind WHERE mark=1 ORDER BY usetimes DESC "];
	NSMutableArray *array=[[NSMutableArray alloc] init];
	FMResultSet *resultSet=[[SQLiteHelper getDatabase] executeQuery:sql];
    
	while ([resultSet next]) { 
        KindWrapper *kind = [[KindWrapper alloc] init];
        
        kind.id = [resultSet intForColumn:@"id"];
        kind.kindName = [resultSet stringForColumn:@"kindname"];
        kind.name = [resultSet stringForColumn:@"name"];
        kind.parentId = [resultSet intForColumn:@"parentid"];
        kind.type = [resultSet stringForColumn:@"type"];
        kind.useTimes = [resultSet intForColumn:@"usetimes"];
        kind.mark = [resultSet boolForColumn:@"mark"];
        
        if (kind.kindName ==nil) {
			continue;
		} 
        [array addObject:kind];
        [kind release]; 
	} 	   
	
	return array;
}


-(bool)updateKind:(KindWrapper *)kind
{
    NSString *sqlString =[NSString string];
    sqlString = [sqlString stringByAppendingFormat:@"UPDATE kind SET kindname ='%@'",kind.kindName];
    sqlString = [sqlString stringByAppendingFormat:@" ,name='%@'",kind.name];
    sqlString = [sqlString stringByAppendingFormat:@" ,parentid=%d",kind.parentId];
    sqlString = [sqlString stringByAppendingFormat:@" ,type='%@'",kind.type];
    sqlString = [sqlString stringByAppendingFormat:@" ,usetimes=%d",kind.useTimes];
    sqlString = [sqlString stringByAppendingFormat:@" ,mark=%d",kind.mark];
    sqlString = [sqlString stringByAppendingFormat:@" WHERE id=%d",kind.id];

    return [SQLiteHelper executeSql:sqlString];
}

#pragma mark - * Account Table

-(NSMutableArray *) getAccountArray:(NSString *)strWhere {
	//SQL
	NSString *sql=@"SELECT * FROM account ";
	NSMutableArray *dataArray=[[NSMutableArray alloc] init];
    
    if ([strWhere length] !=0 ) {
		sql=[sql stringByAppendingString:@" WHERE "];
		sql=[sql stringByAppendingString:strWhere];
	} 
    
	FMResultSet *resultSet=[[SQLiteHelper getDatabase] executeQuery:sql];
	while ([resultSet next]) {
        AccountWrapper *object=[[AccountWrapper alloc] init];
		object.id=[resultSet intForColumn:@"id"];
		object.name=[resultSet stringForColumn:@"name"];
        object.money = [resultSet doubleForColumn:@"money"]; 
        object.moneyKind=[resultSet stringForColumn:@"money_kind"];
        object.alarmValid = [resultSet boolForColumn:@"alarm_valid"];
        object.moneyAlarm =[resultSet doubleForColumn:@"money_alarm"];
        object.moneyStart =[resultSet doubleForColumn:@"money_start"];
        object.moneyTransferIn = [resultSet doubleForColumn:@"money_transferin"];
        object.moneyTransferOut =[resultSet doubleForColumn:@"money_transferout"];
        object.moneyIncome = [resultSet doubleForColumn:@"money_income"];
        object.moneyExpense = [resultSet doubleForColumn:@"money_expense"];
        object.system =[resultSet boolForColumn:@"system"];
        object.type =[resultSet stringForColumn:@"type"];
		         
        [dataArray addObject:object]; 		 
		[object release];  
	}
	
	return dataArray;
}

-(bool)accountAdd:(AccountWrapper *) account {
    
    return [[SQLiteHelper getDatabase] executeUpdate:@"INSERT INTO account(name,money,money_kind,money_alarm,alarm_valid,money_start,money_transferin,money_transferout,money_income,money_expense,system,type) values (?,?,?,?,?,?,?,?,?,?,?,?)",
            account.name,
            [NSNumber numberWithDouble:account.money],
            account.moneyKind,
            [NSNumber numberWithDouble:account.moneyAlarm],
            [NSNumber numberWithBool:account.alarmValid],
            [NSNumber numberWithDouble:account.moneyStart],
            [NSNumber numberWithDouble:account.moneyTransferIn],
            [NSNumber numberWithDouble:account.moneyTransferOut],
            [NSNumber numberWithDouble:account.moneyIncome],
            [NSNumber numberWithDouble:account.moneyExpense],
            [NSNumber numberWithBool:account.system],
            account.type];
}

-(bool)accountDelete:(NSInteger)accountId
{
    NSString *sqlString=[NSString string];
    
    sqlString = [sqlString stringByAppendingFormat:@"DELETE FROM account WHERE id=%d",accountId];
    return [SQLiteHelper executeSql:sqlString];
}

#pragma mark - * Account Type Table
-(NSMutableArray *) getAccountTypeArray
{
    //SQL
	NSString *sql=@"SELECT * FROM accounttype ";
	NSMutableArray *dataArray=[[NSMutableArray alloc] init];
    
	FMResultSet *resultSet=[[SQLiteHelper getDatabase] executeQuery:sql];
	while ([resultSet next]) {
        AccountType *object=[[AccountType alloc] init];
		object.id=[resultSet intForColumn:@"typeid"];
		object.name=[resultSet stringForColumn:@"typename"];
        
        [dataArray addObject:object]; 		 
		[object release];  
	}
    
    // Sort the Array
	NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"id" ascending:YES];
	NSArray *sortDescriptors = [NSArray arrayWithObject:sortDescriptor];
	[dataArray sortUsingDescriptors:sortDescriptors];
	[sortDescriptor release];
	
	return dataArray;
}

#pragma mark - * Record Table

-(NSArray *) getRecord:(NSString *)strWhere {

	[Record recordRemoveAll];//清空记录
	NSDateFormatter *dateFormatter=[[NSDateFormatter alloc] init];//格式化日期
	NSMutableArray * recordList=[NSMutableArray array];
	NSString *sql=@"SELECT * FROM record ";//SQL
	
	if ([strWhere length] !=0 ) {
		sql=[sql stringByAppendingString:@" WHERE "];
		sql=[sql stringByAppendingString:strWhere];
	}
	
	
	FMResultSet *resultSet=[SQLiteHelper query:sql];
	while ([resultSet next]) {
		RecordWrapper *recordWrapper=[[RecordWrapper alloc] init];
		recordWrapper.id=[resultSet intForColumn:@"id"];
		recordWrapper.type=[resultSet stringForColumn:@"type"];
		recordWrapper.kind=[resultSet stringForColumn:@"kind"];
		recordWrapper.subKind=[resultSet stringForColumn:@"sub_kind"];
		recordWrapper.money=[resultSet doubleForColumn:@"money"];
		recordWrapper.remark=[resultSet stringForColumn:@"remark"];
		recordWrapper.account=[resultSet stringForColumn:@"account"];
		recordWrapper.datetime=[resultSet stringForColumn:@"datetime"]; 
        recordWrapper.latitude=[[resultSet stringForColumn:@"latitude"] doubleValue];
        recordWrapper.longitude=[[resultSet stringForColumn:@"longitude"] doubleValue];
		
		if (recordWrapper.datetime ==nil) {
			continue;
		}
		
		[dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss Z"];
		recordWrapper.date=[dateFormatter dateFromString:recordWrapper.datetime]; 
		[dateFormatter setDateFormat:@"yyyy年MM月dd日"];
		NSString *dateStr = [dateFormatter stringFromDate:recordWrapper.date];
		
		Record *record=[Record recordNamed:dateStr];
		if (record ==nil) {
			record=[Record newRecordWithName:dateStr]; 
			[recordList addObject:record];
			[record release];
		}
		
		[record addRecordWrapper:dateStr recordWrapper:recordWrapper]; 
		[recordWrapper release];  
	}
	
	// Sort the records
	NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"name" ascending:NO];
	NSArray *sortDescriptors = [NSArray arrayWithObject:sortDescriptor];
	[recordList sortUsingDescriptors:sortDescriptors];
	[sortDescriptor release];
	
	[dateFormatter release];
	
	return recordList;
}

-(NSMutableArray *) getRecordArray:(NSString *)strWhere {
	//SQL
	NSString *sql=@"SELECT * FROM record ";
	NSMutableArray *recordArray=[[NSMutableArray alloc] init];
    NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
    
    [formatter setDateStyle:kCFDateFormatterFullStyle];
    [formatter setTimeStyle:kCFDateFormatterFullStyle];
    
    if ([strWhere length] !=0 ) {
		sql=[sql stringByAppendingString:@" WHERE "];
		sql=[sql stringByAppendingString:strWhere];
	} 
    
	FMResultSet *resultSet=[[SQLiteHelper getDatabase] executeQuery:sql];
	while ([resultSet next]) {
		RecordWrapper *recordWrapper=[[RecordWrapper alloc] init];
		recordWrapper.id=[resultSet intForColumn:@"id"];
		recordWrapper.type=[resultSet stringForColumn:@"type"];
		recordWrapper.kind=[resultSet stringForColumn:@"kind"];
		recordWrapper.subKind=[resultSet stringForColumn:@"sub_kind"];
		recordWrapper.money=[resultSet doubleForColumn:@"money"];
		recordWrapper.remark=[resultSet stringForColumn:@"remark"];
        recordWrapper.money_kind=[resultSet stringForColumn:@"money_kind"];
		recordWrapper.account=[resultSet stringForColumn:@"account"];
        recordWrapper.latitude=[[resultSet stringForColumn:@"latitude"] doubleValue];
        recordWrapper.longitude=[[resultSet stringForColumn:@"longitude"] doubleValue];
        recordWrapper.imageData=[resultSet dataForColumn:@"image"];
        recordWrapper.year=[resultSet intForColumn:@"year"];
        recordWrapper.month=[resultSet intForColumn:@"month"];
        recordWrapper.day=[resultSet intForColumn:@"day"];
        recordWrapper.datetime = [resultSet stringForColumn:@"datetime"];
        
		recordWrapper.date=[formatter dateFromString:recordWrapper.datetime];
        [recordArray addObject:recordWrapper]; 		 
		[recordWrapper release];  
	}
	
	// Sort the records
	NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"id" ascending:NO];
	NSArray *sortDescriptors = [NSArray arrayWithObject:sortDescriptor];
	[recordArray sortUsingDescriptors:sortDescriptors];
	[sortDescriptor release];
	
    [formatter release]; 
	
	return recordArray;
}

-(NSMutableArray *) getRecordArrayBySql:(NSString *)sql {
	//SQL
	NSMutableArray *recordArray=[[NSMutableArray alloc] init];
    NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
    
    [formatter setDateStyle:kCFDateFormatterFullStyle];
    [formatter setTimeStyle:kCFDateFormatterFullStyle];
       
	FMResultSet *resultSet=[[SQLiteHelper getDatabase] executeQuery:sql];
	while ([resultSet next]) {
		RecordWrapper *recordWrapper=[[RecordWrapper alloc] init];
		recordWrapper.id=[resultSet intForColumn:@"id"];
		recordWrapper.type=[resultSet stringForColumn:@"type"];
		recordWrapper.kind=[resultSet stringForColumn:@"kind"];
		recordWrapper.subKind=[resultSet stringForColumn:@"sub_kind"];
		recordWrapper.money=[resultSet doubleForColumn:@"money"];
		recordWrapper.remark=[resultSet stringForColumn:@"remark"];
        recordWrapper.money_kind = [resultSet stringForColumn:@"money_kind"];
		recordWrapper.account=[resultSet stringForColumn:@"account"];
        recordWrapper.latitude=[[resultSet stringForColumn:@"latitude"] doubleValue];
        recordWrapper.longitude=[[resultSet stringForColumn:@"longitude"] doubleValue];
        recordWrapper.imageData=[resultSet dataForColumn:@"image"];
        recordWrapper.year=[resultSet intForColumn:@"year"];
        recordWrapper.month=[resultSet intForColumn:@"month"];
        recordWrapper.day=[resultSet intForColumn:@"day"];
        recordWrapper.datetime = [resultSet stringForColumn:@"datetime"];
            
		recordWrapper.date=[formatter dateFromString:recordWrapper.datetime];
        [recordArray addObject:recordWrapper]; 		 
		[recordWrapper release];  
	}
	
	// Sort the records
	NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"id" ascending:NO];
	NSArray *sortDescriptors = [NSArray arrayWithObject:sortDescriptor];
	[recordArray sortUsingDescriptors:sortDescriptors];
	[sortDescriptor release];
	
    [formatter release]; 
	
	return recordArray;
}

-(bool)recordAdd:(RecordWrapper *) record {
    
    NSCalendar *calendar = [[NSCalendar alloc] initWithCalendarIdentifier:NSGregorianCalendar];
	NSDateComponents *dateComponents = [calendar components:(NSYearCalendarUnit | NSMonthCalendarUnit | NSDayCalendarUnit | NSWeekdayCalendarUnit) fromDate:record.date];
	record.year = [dateComponents year];
    record.month = [dateComponents month];
	record.day = [dateComponents day];
	[calendar release];
 
    return [[SQLiteHelper getDatabase] executeUpdate:@"INSERT INTO record(type,kind,sub_kind,money,remark,money_kind,account,latitude,longitude,image,year,month,day,datetime) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
            record.type,
            record.kind,
            record.subKind,
            [NSNumber numberWithDouble:record.money],
            record.remark,
            record.money_kind,
            record.account,
            [NSNumber numberWithDouble:record.latitude],
            [NSNumber numberWithDouble:record.longitude],
            record.imageData,
            [NSNumber numberWithInt:record.year],
            [NSNumber numberWithInt:record.month],
            [NSNumber numberWithInt:record.day],
            record.datetime];
}

-(bool)recordDelete:(NSInteger)recordId
{
    NSString *sqlString=[NSString string];
    
    sqlString = [sqlString stringByAppendingFormat:@"DELETE FROM record WHERE id=%d",recordId];
    return [SQLiteHelper executeSql:sqlString];
}

-(bool)recordUpdate:(RecordWrapper *) record
{
    NSCalendar *calendar = [[NSCalendar alloc] initWithCalendarIdentifier:NSGregorianCalendar];
	NSDateComponents *dateComponents = [calendar components:(NSYearCalendarUnit | NSMonthCalendarUnit | NSDayCalendarUnit | NSWeekdayCalendarUnit) fromDate:record.date];
	record.year = [dateComponents year];
    record.month = [dateComponents month];
	record.day = [dateComponents day];
	[calendar release];
    
    return [[SQLiteHelper getDatabase] executeUpdate:@"UPDATE record SET type=?,kind=?,sub_kind=?,money=?,remark=?,money_kind=?,account=?,latitude=?,longitude=?,image=?,year=?,month=?,day=?,datetime=? where id=? ",
            record.type,
            record.kind,
            record.subKind,
            [NSNumber numberWithDouble:record.money],
            record.remark,
            record.money_kind,
            record.account,
            [NSNumber numberWithDouble:record.latitude],
            [NSNumber numberWithDouble:record.longitude],
            record.imageData,
            [NSNumber numberWithInt:record.year],
            [NSNumber numberWithInt:record.month],
            [NSNumber numberWithInt:record.day],
            record.datetime,
            [NSNumber numberWithInt:record.id]];

}

#pragma mark - * Record Type Table
-(NSMutableArray *) getRecordTypeArray
{
    //SQL
	NSString *sql=@"SELECT * FROM recordtype ";
	NSMutableArray *dataArray=[[NSMutableArray alloc] init];
    
	FMResultSet *resultSet=[[SQLiteHelper getDatabase] executeQuery:sql];
	while ([resultSet next]) {
        AccountWrapper *object=[[AccountWrapper alloc] init];
		object.id=[resultSet intForColumn:@"id"];
		object.name=[resultSet stringForColumn:@"name"];
        
        [dataArray addObject:object]; 		 
		[object release];  
	}
    
    // Sort the Array
	NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"name" ascending:YES];
	NSArray *sortDescriptors = [NSArray arrayWithObject:sortDescriptor];
	[dataArray sortUsingDescriptors:sortDescriptors];
	[sortDescriptor release];
	
	return dataArray;
}

#pragma mark -
#pragma mark 内存管理

-(void) dealloc {

	[super dealloc];
}


@end
